/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/07/31
 */
package vn.smartware.co.server.dao.registration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.dto.MstDrinkDTO;
import vn.smartware.co.dto.MstPriceDTO;
import vn.smartware.co.forms.RegisterDrinkForm;
import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.java.net.commons.Utils;
import vn.smartware.co.server.sql.factory.AbstractRegisterDrinkSQLManager;

/**
 * @author thuctt
 * RegisterDrinkSQLManager
 */
public class RegisterDrinkSQLManager extends AbstractRegisterDrinkSQLManager {

	private static Log log = LogFactory.getLog(RegisterDrinkSQLManager.class);
	
	/**
	 * makeGetUnitListSQL
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetUnitListSQL(LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" UNIT_CD, ")
		.append(" NAME, ")
		.append(" DESCRIPTION ")
		
		.append(" FROM MSTUNIT ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		;
		
		buf.append(" ORDER BY UNIT_CD ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * get drink list
	 * @param form 
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetDrinkListSQL(RegisterDrinkForm form, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" DRINK.DRINK_NO, ")
		.append(" DRINK.NAME, ")
		.append(" DRINK.DRINK_TYPE, ")
		.append(" CLS1.NAME AS DRINK_TYPE_NAME,")
		.append(" DRINK.DESCRIPTION, ")
		.append(" DRINK.UNIT_CD, ")
		.append(" UNIT.NAME AS UNIT_NAME, ")
		.append(" PRICE.PRICE_NO, ")
		.append(" PRICE.PRICE, ")
		.append(" PRICE.MONEY_TYPE, ")
		.append(" CLS2.NAME AS MONEY_TYPE_NAME ")

		
		.append(" FROM MSTDRINK DRINK ")
		
		.append(" LEFT OUTER JOIN MSTDRINKPRICE PRICE ")
		.append(" ON PRICE.DRINK_NO = DRINK.DRINK_NO AND PRICE.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS1 ")
		.append(" ON CLS1.KEY1 = 'DRINK_TYPE' AND CLS1.VALUE = DRINK.DRINK_TYPE ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS2 ")
		.append(" ON CLS2.KEY1 = 'MONEY_TYPE' AND CLS2.VALUE = PRICE.MONEY_TYPE ")
		
		.append(" LEFT OUTER JOIN MSTUNIT UNIT ")
		.append(" ON UNIT.UNIT_CD = DRINK.UNIT_CD AND UNIT.DELETE_DATE IS NULL ")
		
		.append(" WHERE DRINK.DELETE_DATE IS NULL ")
		;
		
		if (!Utils.isNullEmpty(form.getNoStart())){
			buf.append(" AND DRINK.DRINK_NO >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getNoEnd())){
			buf.append(" AND DRINK.DRINK_NO <= ? ");
		}
		
		buf.append(" ORDER BY DRINK.DRINK_TYPE, DRINK.NAME, PRICE.MONEY_TYPE ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			if (!Utils.isNullEmpty(form.getNoStart())){
				ps.setInt(i++, Integer.parseInt(form.getNoStart()));
				log.info("DRINK_NO >= " + form.getNoStart());
			}
			
			if (!Utils.isNullEmpty(form.getNoEnd())){
				ps.setInt(i++, Integer.parseInt(form.getNoEnd()));
				log.info("DRINK_NO <= " + form.getNoEnd());
			}
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeleteFoodSQL
	 * @param drinkNo
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeDeleteDrinkSQL(String drinkNo, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTDRINK SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE DRINK_NO = ? AND DELETE_DATE IS NULL ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, drinkNo);
			log.info("DRINK_NO = " + drinkNo);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeRegisterDrinkSQL
	 * @param drink
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeRegisterDrinkSQL(MstDrinkDTO drink, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MSTDRINK ( ")
		.append(" NAME, DRINK_TYPE, DESCRIPTION, UNIT_CD, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, drink.getName());
			log.info("NAME = " + drink.getName());
			
			ps.setString(i++, drink.getType());
			log.info("DRINK_TYPE = " + drink.getType());
			
			ps.setString(i++, drink.getDescription());
			log.info("DESCRIPTION = " + drink.getDescription());
			
			ps.setString(i++, drink.getUnitCd());
			log.info("UNIT_CD = " + drink.getUnitCd());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeUpdateDrinkSQL
	 * @param drink
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeUpdateDrinkSQL(MstDrinkDTO drink, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTDRINK SET ")
		.append(" NAME = ?, DRINK_TYPE = ?, DESCRIPTION = ?, UNIT_CD = ?, ")
		.append(" UPDATE_USER_ID = ?, UPDATE_DATE = SYSDATE() ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		.append(" AND DRINK_NO = ? ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, drink.getName());
			log.info("NAME = " + drink.getName());
			
			ps.setString(i++, drink.getType());
			log.info("DRINK_TYPE = " + drink.getType());
			
			ps.setString(i++, drink.getDescription());
			log.info("DESCRIPTION = " + drink.getDescription());
			
			ps.setString(i++, drink.getUnitCd());
			log.info("UNIT_CD = " + drink.getUnitCd());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			ps.setString(i++, drink.getDrinkNo());
			log.info("DRINK_NO = " + drink.getDrinkNo());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * getMaxDrinkNoSQL
	 * @param conn
	 * @throws ServerException 
	 */
	public void getMaxDrinkNoSQL(Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT MAX(DRINK_NO) AS DRINK_NO ")
		.append(" FROM MSTDRINK ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeRegisterPriceSQL
	 * @param drinkNo
	 * @param price
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeRegisterPriceSQL(String drinkNo, MstPriceDTO price, LoginDTO user,
			Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO MSTDRINKPRICE ( ")
		.append(" DRINK_NO, MONEY_TYPE, PRICE, ")
		.append(" INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES ( ")
		.append(" ?, ?, ?, ?, SYSDATE(), ?, SYSDATE() )")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, drinkNo);
			log.info("DRINK_NO = " + drinkNo);
			
			ps.setString(i++, price.getMoneyType());
			log.info("MONEY_TYPE = " + price.getMoneyType());
			
			ps.setString(i++, price.getPrice());
			log.info("PRICE = " + price.getPrice());
			
			ps.setInt(i++, user.getUserId());
			log.info("INSERT_USER_ID = " + user.getUserId());
			
			ps.setInt(i++, user.getUserId());
			log.info("UPDATE_USER_ID = " + user.getUserId());
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeDeletePriceListSQL
	 * @param roomNo
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeDeletePriceListSQL(String roomNo, LoginDTO user,
			Connection conn) throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MSTDRINKPRICE SET DELETE_DATE = SYSDATE() ")
		.append(" WHERE DRINK_NO = ? AND DELETE_DATE IS NULL ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, roomNo);
			log.info("ROOM_NO = " + roomNo);
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
}
